package DTO;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteException;
import android.util.Log;
import database.DatabaseHelper;

public class StudyResultDTO {

	//private String LicenseType;
	private String QuestionID;
	private ArrayList<String> AnswerIDs;

	//Property LicenseType
	//public String getLicenseType() { return LicenseType; }
	//public void setLicenseType(String licenseType) { LicenseType = licenseType; }
	
	//Property QuestionID
	public String getQuestionID() { return QuestionID; }
	public void setQuestionID(String questionID) { QuestionID = questionID; }
	
	//Property AnswerID
	public ArrayList<String> getAnswerIDs() { return AnswerIDs; }
	public void setAnswerIDs(ArrayList<String> answerIDs) { AnswerIDs = answerIDs; }
	
	public static StudyResultDTO getStudyResultByQuestionID(Context context, String questionID, String licenseType){

		StudyResultDTO studyResults = new StudyResultDTO();
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
	 	
	 	Cursor cursor1 = myDbHelper.getMyDataBase().rawQuery("select * from StudyResults where QuestionID = '" + questionID + "' and LicenseType = '"+licenseType+"'", null);
		if(cursor1.moveToFirst()){
			studyResults.setQuestionID(cursor1.getString(cursor1.getColumnIndex("QuestionID")));
			ArrayList<String> listAnswerIDs = new ArrayList<String>();
			do{
				//Set value to properties
				listAnswerIDs.add(cursor1.getString(cursor1.getColumnIndex("AnswerID")));
			}while(cursor1.moveToNext());
			studyResults.setAnswerIDs(listAnswerIDs);
		}
	 	myDbHelper.close();
	 	return studyResults;
	}
	
	public static ArrayList<StudyResultDTO> getAllStudyResult(Context context, String licenseType){
		ArrayList<StudyResultDTO> listStudyResults = new ArrayList<StudyResultDTO>();
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
	 	String query = "select * from StudyResults where LicenseType = '"+licenseType+"' order by QuestionID";
	 	Cursor cursor1 = myDbHelper.getMyDataBase().rawQuery(query, null);
		
	 	if(cursor1.moveToFirst()){
	 		do{
	 			StudyResultDTO studyResult = new StudyResultDTO();
	 			studyResult.setQuestionID(cursor1.getString(cursor1.getColumnIndex("QuestionID")));
	 			listStudyResults.add(studyResult);
	 		}while (cursor1.moveToNext());
		}
	 	myDbHelper.close();
	 	return listStudyResults;
	}
	
	public static void insertStudyResult(Context context, StudyResultDTO studyResult, String licenseType){
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
	 	
	 	myDbHelper.getMyDataBase().beginTransaction();
	 	try{
	 		String querySelect = null;
	 		String query = null;
	 		
	 		//Check and delete data existed
		 	querySelect = "SELECT count(AnswerID) FROM StudyResults WHERE QuestionID = '" + studyResult.getQuestionID() + "' and LicenseType = '"+licenseType+"'";
		 	Cursor cursor = myDbHelper.getMyDataBase().rawQuery(querySelect, null);
		 	if(cursor.moveToFirst())
		 	{
		 		if(cursor.getInt(0) != 0){
			 		query = "DELETE FROM StudyResults WHERE QuestionID = '" + studyResult.getQuestionID() + "' and LicenseType = '"+licenseType+"'";
			 		myDbHelper.getMyDataBase().execSQL(query);
			 	}
		 	}
		 	//Insert data
	 		for(int i = 0; i < studyResult.getAnswerIDs().size(); i++){
	 			query = "INSERT INTO StudyResults(LicenseType, QuestionID, AnswerID) VALUES('"+licenseType+"', '" + studyResult.getQuestionID() + "', '" + studyResult.getAnswerIDs().get(i) + "')";
	 			myDbHelper.getMyDataBase().execSQL(query);
	 		}

	 		myDbHelper.getMyDataBase().setTransactionSuccessful();
	 	}catch(SQLiteException e){
	 		Log.e("Traffic Learning - SQLite Error", e.toString());
	 	}finally{
	 		myDbHelper.getMyDataBase().endTransaction();
	 		myDbHelper.close();
	 	}
	}
	
	public static void deleteStudyResult(Context context, StudyResultDTO studyResult, String licenseType){
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
	 	
	 	myDbHelper.getMyDataBase().beginTransaction();
	 	try{
	 		String querySelect = null;
	 		String query = null;
	 		
	 		//Check and delete data existed
		 	querySelect = "SELECT count(AnswerID) FROM StudyResults WHERE QuestionID = '" + studyResult.getQuestionID() + "' and LicenseType = '"+licenseType+"'";
		 	Cursor cursor = myDbHelper.getMyDataBase().rawQuery(querySelect, null);
		 	if(cursor.moveToFirst())
		 	{
		 		if(cursor.getInt(0) != 0){
			 		query = "DELETE FROM StudyResults WHERE QuestionID = '" + studyResult.getQuestionID() + "' and LicenseType = '"+licenseType+"'";
			 		myDbHelper.getMyDataBase().execSQL(query);
			 	}
		 	}
	 		myDbHelper.getMyDataBase().setTransactionSuccessful();
	 	}catch(SQLiteException e){
	 		Log.e("Traffic Learning - SQLite Error", e.toString());
	 	}finally{
	 		myDbHelper.getMyDataBase().endTransaction();
	 		myDbHelper.close();
	 	}
	}
	public static void deleteByLicenseType(Context context, String licenseType){
		DatabaseHelper myDbHelper = DatabaseHelper.getInstance(context);
	 	try {
	 		myDbHelper.openDatabase();
	 	}catch(SQLException sqle){
	 		throw sqle;
	 	}
	 	
	 	myDbHelper.getMyDataBase().beginTransaction();
	 	try{
	 		String query = null;
	 		query = "DELETE FROM StudyResults WHERE LicenseType = '"+licenseType+"'";
	 		myDbHelper.getMyDataBase().execSQL(query);
	 		myDbHelper.getMyDataBase().setTransactionSuccessful();
	 	}catch(SQLiteException e){
	 		Log.e("Traffic Learning - SQLite Error", e.toString());
	 	}finally{
	 		myDbHelper.getMyDataBase().endTransaction();
	 		myDbHelper.close();
	 	}
	}
}
